import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;

import java.io.File;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.*;
import java.util.ArrayList;

public class MapperMaker {
//	<?xml version="1.0" encoding="UTF-8" ?>
//	<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

    /**
     * 数据库配置信息
     */
    static String url = "jdbc:mysql://118.126.92.157:3306/szzj";
    static String user = "root";// 用户名
    static String password = "gpdi#zixun88";//密码
   //~配置完成。


    static String key = "id";   //每个表的主键必须是id(自增)
    static String tableName = null;    //表名
    /**
     * @throws Exception
     */
    public static String getXml() throws Exception {
        tableName = tableName.toLowerCase();
        ArrayList<String> cols = new ArrayList<String>(); // 列名
        String colAll = "";

        Connection conn = getConnection();
        Statement stmt = conn.createStatement();
        ResultSet pkRSet = stmt.executeQuery("select * from " + tableName
                + " where 1=2");
        ResultSetMetaData rsd = pkRSet.getMetaData();
        int count = rsd.getColumnCount();
        StringBuffer sb = new StringBuffer();
        sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n" +
                "<!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">\n");
        sb.append("\n<mapper namespace=\"" + tableName.toUpperCase() + "\">");
        for (int i = 1; i <= count; i++) {
            cols.add(rsd.getColumnName(i));
            colAll += rsd.getColumnName(i) + ",";
        }
        colAll = colAll.substring(0, colAll.length() - 1);

        // 添加全字段
        sb.append("\n<!-- " + tableName + " 全字段 -->");
        sb.append("\n<sql id=\"" + "BASIC" + "_COL\">");
        sb.append(colAll);
        sb.append("</sql>");

        // <!-- "+tableName+" 选择字段 -->
        sb.append("\n<!-- " + tableName + " 选择字段 -->");
        sb.append("\n<sql id=\"" + "BASIC"
                + "_COLSEL\"><trim suffixOverrides=\",\">");
        for (String c : cols) {
            sb.append("\n<if test=\"" + c + " != null\">");
            sb.append(c + ",");
            sb.append("</if>");
        }
        sb.append("</trim>");
        sb.append("</sql>");

        // 记录数
        sb.append("\n<!-- " + tableName + " 记录数 -->");
        sb.append("\n<select id=\"" + "BASIC"
                + "_CNT\" parameterType=\"hashmap\" resultType=\"long\">");
        sb.append(" select count(1)  from " + tableName + " <where> ");
        for (String c : cols) {
            sb.append("\n<if test=\"" + c + " != null\">");
            sb.append("and " + c + "=#{" + c + "}");
            sb.append("</if>");
        }
        sb.append("</where>");
        sb.append("</select>");

        // 全字段查询
        sb.append("\n<!-- " + tableName + " 全字段查询 -->");
        sb.append("\n<select id=\"" + "BASIC"
                + "_Q\" parameterType=\"hashmap\" resultType=\"hashmap\">");
        sb.append(" <!--引用前边定义的所有字段 --> select  <include refid=\"" + "BASIC"
                + "_COL\" /> from " + tableName + " <where>");
        for (String c : cols) {
            sb.append("\n<if test=\"" + c + " != null\">");
            sb.append("and " + c + "=#{" + c + "}");
            sb.append("</if>");
        }
        sb.append("</where>");
        sb.append("</select>");
        // 选择插入
        sb.append("\n<!-- " + tableName + " 选择字段插入 -->");
        sb.append("\n<insert id=\"" + "BASIC"
                + "_I\" parameterType=\"hashmap\" useGeneratedKeys=\"true\" keyProperty=\"id\">");
        sb.append(" insert into " + tableName + " (");
        sb.append("\n<trim suffixOverrides=\",\">");
        for (String c : cols) {
            sb.append("\n<if test=\"" + c + " != null\">");
            sb.append(c + ",");
            sb.append("</if>");
        }
        sb.append("</trim>");
        sb.append(") values (");
        sb.append("\n<trim suffixOverrides=\",\">");
        for (String c : cols) {
            sb.append("\n<if test=\"" + c + " != null\">");
            sb.append("#{" + c + "},");
            sb.append("</if>");
        }
        sb.append("</trim>)");
        sb.append("</insert>");

        //批量插入
        sb.append("\n<insert id=\"" + "BASIC"
                + "_BATCH_I\" parameterType=\"list\" useGeneratedKeys=\"true\" keyProperty=\"id\">");
        sb.append(" insert into " + tableName + " (");
        sb.append("\n<trim suffixOverrides=\",\">");
        for (String c : cols) {
            if (!c.equals(key)) {
                sb.append(c + ",");
            }
        }
        sb.append("</trim> ) values");
        sb.append("\n  <foreach collection=\"list\" item=\"map\" separator=\",\">(");
        sb.append("\n<trim suffixOverrides=\",\">");

        for (String c : cols) {
            if (!c.equals(key)) {
                sb.append("map." + c + ",");
            }
        }
        sb.append("</trim>");

        sb.append(")\n  </foreach>");
        sb.append(")\n</insert>");

        // 选择修改
        sb.append("\n<!-- " + tableName + " 选择修改 -->");
        sb.append("\n<update id=\"" + "BASIC"
                + "_U\" parameterType=\"hashmap\">");
        sb.append(" update " + tableName + "   set");
        sb.append(" <trim suffixOverrides=\",\">");
        for (String c : cols) {
            sb.append("\n<if test=\"" + c + " != null\">" + c + "=#{" + c + "},</if>");
        }
        sb.append("</trim>");
        sb.append(" where ");
        sb.append(key + "=#{" + key + "}");
        sb.append("</update>");

        // 删除
        sb.append("\n<!-- " + tableName + " 删除 -->");
        sb.append("\n<delete id=\"" + "BASIC"
                + "_D\" parameterType=\"hashmap\">");
        sb.append("delete from " + tableName + " ");
        sb.append(" where ");
        sb.append(key + "=#{" + key + "}");
        sb.append("</delete>");
        sb.append("</mapper>");

        // System.out.println(sb.toString());

        return formatXml(sb.toString());

    }



    private static Connection getConnection() {
        Connection con = null;// 创建一个数据库连接
        PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
        ResultSet result = null;// 创建一个结果集对象
        try {
            //Class.forName("oracle.jdbc.driver.OracleDriver");// 加载Oracle驱动程序
            Class.forName("com.mysql.jdbc.Driver");// 加载Oracle驱动程序
            con = DriverManager.getConnection(url, user, password);// 获取连接
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }
        return con;
    }

    private static String formatXml(String str) throws Exception {
        Document document = null;
        document = DocumentHelper.parseText(str);
        // 格式化输出格式
        OutputFormat format = OutputFormat.createPrettyPrint();
        format.setEncoding("UTF-8");
        StringWriter writer = new StringWriter();
        // 格式化输出流
        XMLWriter xmlWriter = new XMLWriter(writer, format);
        // 将document写入到输出流
        xmlWriter.write(document);
        xmlWriter.close();
        return writer.toString();
    }

    /**
     * src\main\resources\common\mapper\basic 生成单个mapper文件
     * @param tn
     * @throws Exception
     */
    public static void intTable(String tn) throws Exception {
        tableName=tn;
        String str = getXml();
        String dirPath = System.getProperty("user.dir") + "\\src\\main\\resources\\common\\mapper\\basic\\";
        File f = new File(dirPath + MapperMaker.tableName.toUpperCase() + "_Mapper.xml");
        if (f.exists()) {
            System.out.println(MapperMaker.tableName.toUpperCase() + "_Mapper.xml" + "文件已存在，请先删除文件");
            f.delete();
            //return;
        }
        PrintWriter writer = new PrintWriter(dirPath + MapperMaker.tableName.toUpperCase() + "_Mapper.xml", "UTF-8");
        writer.println(str);
        writer.close();
    }

    /**
     * src\main\resources\common\mapper\basic 批量生成mapper文件
     * @throws Exception
     */
    public static void database() throws Exception {
        String str[]=url.split("/");
        String dbName=str[str.length-1];
       ResultSet rs= getConnection().createStatement().executeQuery("select table_name from information_schema.tables where TABLE_SCHEMA='"+dbName+"'");
       while (rs.next()){
           intTable(rs.getString(1));
       }
    }

    public static void main(String[] args)  throws Exception {
       // database();
        String sql="CREATE TABLE a3 (\n" +
                "  ida2 int(11) NOT NULL AUTO_INCREMENT,\n" +
                "  a2col varchar(45) DEFAULT NULL,\n" +
                "  PRIMARY KEY (ida2)\n" +
                ") ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;" ;
        String sql2="CREATE TABLE a4 (\n" +
                "  ida2 int(11) NOT NULL AUTO_INCREMENT,\n" +
                "  a2col varchar(45) DEFAULT NULL,\n" +
                "  PRIMARY KEY (ida2)\n" +
                ") ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;" ;
        getConnection().createStatement().execute(sql+sql2);
    }
}
